cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Table5.log, replace

***Table 5 descriprive statistics
***total number of financing rounds and age at the first financing round
use ./5_postmoney_valuations_failures.dta, clear
merge m:1 orgnr using ./7_population_firmTVPI.dta
keep if _merge==3
drop _merge
gen firmage=aar-founded+1
replace firmage=0 if firmage<0
*393 changes
tsset orgnr time
keep if mv_rounds!=. & mv_rounds!=0
sort orgnr time
bys orgnr: gen total_rounds=_N
bys orgnr: gen first=1 if _n==1
keep if first==1
*45,706 firms
keep orgnr total_rounds vcbacked sample firmage
*HIP firms
outreg2 using ./tables/Table5.xls if sample==1, replace sum(detail) keep (total_rounds firmage) eqkeep(N mean p50)
*HIP & VC-backed
outreg2 using ./tables/Table5.xls if sample==1 & vcbacked==1, append sum(detail) keep (total_rounds firmage) eqkeep(N mean p50)
*Non-HIP firms
outreg2 using ./tables/Table5.xls if sample==0, append sum(detail) keep (total_rounds firmage) eqkeep(N mean p50)

****financing rounds description
use ./6_investors.dta, clear
***identify only entrepreneur's equity
keep if investortype==1
keep orgnr time primamount
rename primamount founderamount
*merge back to the full data
merge 1:1 orgnr time using ./5_postmoney_valuations_failures.dta
drop if _merge==1
drop _merge
replace founderamount=0 if founderamount==.
merge m:1 orgnr using ./7_population_firmTVPI.dta
keep if _merge==3
drop _merge
tsset orgnr time
keep if mv_rounds!=. & mv_rounds!=0
*64,327 financing rounds

gen firmage=aar-founded+1
replace firmage=0 if firmage<0
*393 changes

*consider only non-founder equity
sum founderamount, d
/*
                        founderamount
-------------------------------------------------------------
      Percentiles      Smallest
 1%            0              0
 5%            0              0
10%            0              0       Obs              64,327
25%            0              0       Sum of wgt.      64,327

50%            0                      Mean           174578.2
                        Largest       Std. dev.       9331697
75%            0       1.80e+08
90%       140000       2.50e+08       Variance       8.71e+13
95%       375000       2.55e+08       Skewness       233.5942
99%      1949850       2.30e+09       Kurtosis       57372.98
*/
replace primamount=primamount-founderamount
*10,772 changes
sum primamount, d

bys orgnr firmage: egen primamount2=sum(primamount)
bys orgnr firmage: egen valuation=mean(mv_rounds)

keep orgnr firmage primamount2 valuation sample vcbacked
duplicates drop
*58,953 observations

foreach t in primamount2 valuation {
sum `t', d
return list
sum `t' if `t'>r(p99), d
return list
replace `t'=r(p50) if `t'>r(p50) & `t'!=.
}

foreach t in primamount2 valuation {
replace `t'=`t'/1000000
}

gen ownership=primamount2/valuation
sum ownership, d
replace ownership=1 if ownership>1
*951 changes

gen age=1 if firmage==0 | firmage==1
replace age=2 if firmage==2 | firmage==3
replace age=3 if firmage==4 | firmage==5 | firmage==6
replace age=4 if firmage>6

**for HIP firms
bys age: outreg2 using ./tables/Table5.xls if sample==1, append sum(detail) keep (primamount2 ownership valuation) eqkeep(N mean p50)
**for VC-backed HIP firms
bys age: outreg2 using ./tables/Table5.xls if sample==1 & vcbacked==1, append sum(detail) keep (primamount2 ownership valuation) eqkeep(N mean p50)
**for non-HIP firms
bys age: outreg2 using ./tables/Table5.xls if sample==0, append sum(detail) keep (primamount2 ownership valuation) eqkeep(N mean p50)


****description of secondary trades
use ./5_postmoney_valuations_failures.dta, clear
merge m:1 orgnr using ./7_population_firmTVPI.dta
keep if _merge==3
drop _merge
tsset orgnr time
keep if mv_sec!=.
*43,449 obs
keep orgnr sample vcbacked
duplicates drop
tab sample vc
/*
           |       vcbacked
    sample |         0          1 |     Total
-----------+----------------------+----------
         0 |    13,181         71 |    13,252 non-HIP firms
         1 |    11,087        475 |    11,562 HIP firms
-----------+----------------------+----------
     Total |    24,268        546 |    24,814 

*/

use ./5_postmoney_valuations_failures.dta, clear
merge m:1 orgnr using ./7_population_firmTVPI.dta
keep if _merge==3
drop _merge
tsset orgnr time
keep if mv_sec!=.
*43,449 obs

gen firmage=aar-founded+1
replace firmage=0 if firmage<0
*0 changes

foreach t in mv_sec secamount {
sum `t', d
return list
sum `t' if `t'>r(p99), d
return list
replace `t'=r(p50) if `t'>r(p50) & `t'!=.
}

foreach t in mv_sec secamount {
replace `t'=`t'/1000000
}

**for HIP firms
outreg2 using ./tables/Table5.xls if sample==1, append sum(detail) keep (firmage secamount mv_sec) eqkeep(N mean p50)
**for HIP+VC-backed firms
outreg2 using ./tables/Table5.xls if sample==1 & vcbacked==1, append sum(detail) keep (firmage secamount mv_sec) eqkeep(N mean p50)
**for non-HIP firms
outreg2 using ./tables/Table5.xls if sample==0, append sum(detail) keep (firmage secamount mv_sec) eqkeep(N mean p50)


